<?php


namespace app\home\controller;


use app\base\BaseController;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\Exception;
use think\facade\Db;
use think\facade\View;

/**
 * Class PunterBegin
 * @package app\home\controller
 * 客户期初
 */
class PunterBegin extends BaseController
{
    protected $table='purveyor_begin';
    protected $title='客户期初';
    private $sessionKey='pagePunterBegin';

    /**
     * @return \json|\think\response\View
     * @throws \think\db\exception\DbException
     * date:2024/5/8
     */
    public function index()
    {
        if (request()->isAjax()) {
            $param=$this->param;
            $where[]=['pp.status','>',-1];
            $where[]=['pp.b_type','=',2];
            if (isset($this->param['status'])&&$this->param['status']){
                $where[]=['pp.status','=',$this->param['status']];
            }
            foreach (['p_code','p_name','md_bar_code','md_name','md_brand','md_specs'] as $v){
                if ($v=='md_bar_code'){
                    $k='md.bar_code';
                }else{
                    $k=str_replace('_','.',$v);
                }
                if (isset($this->param[$v])&&$this->param[$v]){
                    $where[]=[$k,'like','%'.$this->param[$v].'%'];
                }
            }
            if (isset($this->param['time'])&&$this->param['time']){
                $where[]=['pp.time','=',$this->param['time']];
            }
            session($this->sessionKey,$where);
            $rows = empty($param['limit']) ? get_config('app.page_size') : $param['limit'];
            $list = Db::name($this->table)->alias('pp')
                ->join('material_dossier md','md.id=pp.material_dossier_id')
                ->join('purveyor p','p.id=pp.purveyor_id','left')
                ->where($where)
                ->order('pp.id DESC')
                ->field('pp.*,md.bar_code,md.name as mdName,md.unit,md.specs,p.name as pName,p.code as pCode')
                ->paginate($rows);
            return table_assign(0, '', $list);
        } else {
            return view();
        }
    }

    /**
     * @return \json|\think\response\View
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     * date:2024/5/8
     * 添加
     */
    public function add()
    {
        $param = get_params();
        if (request()->isAjax()) {
            $param=$this->param;
            $param['update_time']=time();
            Db::startTrans();
            try {
                if ($param['id'] > 0) {
                    Db::name($this->table)->strict(false)->field(true)->update($param);
                    $price=Db::name('purveyor_price')->where('p_type',2)
                        ->where('status',1)->where('purveyor_id',$this->param['purveyor_id'])
                        ->where('material_dossier_id',$param['material_dossier_id'])
                        ->where('time',date('Y-m'))->order('time DESC')->limit(1)->value('price');
                    $price=$price??0;
                    $gzData=[
                        'punter_id'=>$param['purveyor_id'],
                        'material_dossier_id'=>$param['material_dossier_id'],
                        'num'=>$param['num'],
                        'price'=>$price,
                        'countPrice'=>$price*$param['num'],
                        'update_time'=>time()
                    ];
                    Db::name('sale_hanging_bill')->where('punter_begin_id',$this->param['id'])->update($gzData);
                    add_log('edit', $param['id'], $param,$this->title);
                } else {
                    $param['b_type']=2;
                    $param['orderNum']=orderNum('QCH');
                    $param['create_time'] = time();
                    $rid = Db::name($this->table)->strict(false)->field(true)->insertGetId($param);
                    $price=Db::name('purveyor_price')->where('p_type',2)
                        ->where('status',1)->where('purveyor_id',$this->param['purveyor_id'])
                        ->where('material_dossier_id',$param['material_dossier_id'])
                        ->where('time',date('Y-m'))->order('time DESC')->limit(1)->value('price');
                    $price=$price??0;
                    $gzData=[
                        'punter_id'=>$param['purveyor_id'],
                        'material_dossier_id'=>$param['material_dossier_id'],
                        'punter_begin_id'=>$rid,
                        'type'=>'期初',
                        'g_orderNum'=>$param['orderNum'],
                        'num'=>$param['num'],
                        'price'=>$price,
                        'countPrice'=>$price*$param['num'],
                        'orderNum'=>orderNum('GZH'),
                        'orderDate'=>date('Y-m-d'),
                        'status'=>'1',
                        'create_time'=>time(),
                        'update_time'=>time()
                    ];
                    Db::name('sale_hanging_bill')->insert($gzData);//生成销售挂账单
                    add_log('add', $rid, $param,$this->title);
                }
                Db::commit();
            }catch (Exception $e){
                Db::rollback();
                return to_assign(1,'操作失败:'.$e->getMessage());
            }

            return to_assign();
        } else {
            $id = isset($param['id']) ? $param['id'] : 0;
            if ($id > 0) {
                $detail = Db::name($this->table)->where('id', $id)->find();
                View::assign('detail', $detail);
            }
            $punterList=Db::name('purveyor')->where('dw_type',2)->where('status',1)->field('id,name')->select();
            $matList=Db::name('material_dossier')->where('status','1')->select();
            View::assign('id', $id);
            View::assign('punterList', $punterList);
            View::assign('matList', $matList);
            return view('form');
        }
    }

    /**
     * @return \json
     * @throws \think\db\exception\DbException
     * date:2024/5/8
     * 删除
     */
    public function delete()
    {
        if (request()->isDelete()) {
            $id = get_params("id");
            try {
                Db::name($this->table)->where('id','in',$id)->update(['status'=>-1,'deleted'=>time()]);
                Db::name('sale_hanging_bill')->where('punter_begin_id','in',$id)->update(['status'=>-1,'deleted'=>time()]);
                add_log('delete', $id, request()->param(),$this->title);
            }catch (\Exception $e){
                return to_assign(1, "删除失败".$e->getMessage());
            }
            return to_assign(0, "删除成功");
        } else {
            return to_assign(1, "错误的请求");
        }
    }

    public function importData()
    {

        Db::StartTrans();
        try {
            if (!isset($_FILES['file'])){
                throw new Exception('请选择上传文件');
            }
            //限制上传表格类型
            $fileExtendName = substr(strrchr($_FILES['file']["name"], '.'), 1);
            //application/vnd.ms-excel  为xls文件类型
            if ($fileExtendName != 'xlsx') {
                throw new Exception('必须为excel表格，且必须为xlsx格式！');
            }

            if (is_uploaded_file($_FILES['file']['tmp_name'])) {
                // 有Xls和Xlsx格式两种
//            $objReader = \IOFactory::createReader('Xlsx');
                $objReader = IOFactory::createReader('Xlsx');

                $filename = $_FILES['file']['tmp_name'];

                $objPHPExcel = $objReader->setReadDataOnly(true)->load($filename);  //$filename可以是上传的表格，或者是指定的表格

                $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
                $highestRow = $sheet->getHighestRow();       // 取得总行数
                $importData = [];
                $gzData=[];
                //循环读取excel表格，整合成数组。如果是不指定key的二维，就用$data[i][j]表示。
                for ($j = 2; $j <= $highestRow; $j++) {
                    $purveyor_id=$objPHPExcel->getActiveSheet()->getCell("A" . $j)->getFormattedValue();
                    $purveyor_id=Db::name('purveyor')
                        ->where('dw_type',2)
                        ->where('status',1)->where('id',$purveyor_id)->value('id');
                    if (!$purveyor_id){
                        throw new Exception('客户id有误,行:'.($j-1));
                    }
                    $bar_code=$objPHPExcel->getActiveSheet()->getCell("B" . $j)->getFormattedValue();
                    $mdId=Db::name('material_dossier')
                        ->where('status',1)->where('bar_code',$bar_code)->value('id');
                    if (!$mdId){
                        throw new Exception('物料编码有误,行:'.($j-1));
                    }
                    $num=(int)$objPHPExcel->getActiveSheet()->getCell("C" . $j)->getFormattedValue();
                    if (!$num){
                        throw new Exception('数量有误,行:'.($j-1));
                    }

                    $price=Db::name('purveyor_price')->where('p_type',2)
                        ->where('status',1)->where('purveyor_id',$purveyor_id)
                        ->where('material_dossier_id',$mdId)
                        ->where('time',date('Y-m'))->order('time DESC')->limit(1)->value('price');
                    $price=$price??0;
                    $qch=orderNum('QCH');
                    $importData[]=$bData=[
                        'purveyor_id'=>$purveyor_id,
                        'material_dossier_id'=>$mdId,
                        'num'=>$num,
                        'pbOrderNum'=>$qch,
                        'b_type'=>2,
                        'status'=>1,
                        'deleted'=>0,
                        'create_time'=>time(),
                        'update_time'=>time()
                    ];

                    $rId=Db::name($this->table)->insertGetId($bData);
                    $gzData[]=[
                        'punter_id'=>$purveyor_id,
                        'material_dossier_id'=>$mdId,
                        'punter_begin_id'=>$rId,
                        'type'=>'期初',
                        'g_orderNum'=>$qch,
                        'num'=>$num,
                        'price'=>$price,
                        'countPrice'=>$price*$num,
                        'orderNum'=>orderNum('GZH'),
                        'orderDate'=>date('Y-m-d'),
                        'status'=>'1',
                        'create_time'=>time(),
                        'update_time'=>time()
                    ];
                }
                if ($gzData){
                    Db::name('sale_hanging_bill')->insertAll($gzData);//生成销售挂账单
                }
                if ($importData){
                    add_log('import', '', $importData,$this->title.'->导入');
                }

                Db::commit();
            }
        }catch (Exception $e){
            Db::rollback();
            return to_assign(1,'操作失败:'.$e->getMessage());
        }
        return to_assign();
    }

    public function exportData()
    {
        $where=session($this->sessionKey);
        $mStatus=['','草稿','已确认'];
        $list = Db::name($this->table)->alias('pp')
            ->join('material_dossier md','md.id=pp.material_dossier_id')
            ->join('purveyor p','p.id=pp.purveyor_id','left')
            ->where($where)
            ->order('pp.id DESC')
            ->field('pp.*,md.bar_code,md.name as mdName,md.unit,md.specs,p.name as pName,p.code as pCode')
            ->select();
        $title = [
            'id' => 'ID',
            'pName' => '客户名称',
            'bar_code' => '产品编码',
            'mdName' => '产品名称',
            'specs' => '规格型号',
            'unit' => '单位',
            'num' => '数量',
        ];
        $name =$this->title;
        exportXlsData($name,$title,$list);
        exit();
    }
}